package com.zzxz.practicaltrainingsys.mapper;

import com.zzxz.practicaltrainingsys.entity.*;
import org.apache.ibatis.annotations.*;

import java.util.Date;
import java.util.List;
import java.util.Map;

@Mapper
public interface ProjectMapper {
    //查询所有项目
    @Select("select * from project")
    public List<Project> getProject();

    //获取所有项目名称
    @Select("select DISTINCT project_name from project")
    public List<String> getProjectNames();


    //查询项目开始时间和结束时间
    @Select("SELECT p.project_start as projectStart, p.project_end as projectEnd, p.project_id " +
            "FROM project p " +
            "WHERE p.project_id = #{projectId} " +
            "LIMIT 1")
    Map<String, Object> getProjectTime(@Param("projectId") int projectId);

    //新增项目
    @Insert("insert into project (project_name,project_introduction,project_start,project_end,school_id,college_id,grade_id,major_id,topic_id,teacher_id,create_time,project_note)\n" +
    "values(#{project_name},#{project_introduction},#{project_start},#{project_end},#{school_id},#{college_id},#{grade_id},#{major_id},#{topic_id},#{teacher_id},#{create_time},#{project_note})")
    @Options(useGeneratedKeys = true, keyProperty = "project_id", keyColumn = "project_id")
    int insertProject(Project project);  // 返回值是受影响行数，但自增ID会回填到 project 对象中

    //编辑项目
    @Update("update project set " +
            "project_name=#{project_name}," +
            "project_introduction=#{project_introduction}," +
            "project_start=#{project_start}," +
            "project_end=#{project_end}," +
            "school_id=#{school_id}," +
            "college_id=#{college_id}," +
            "grade_id=#{grade_id}," +
            "major_id=#{major_id}," +
            "topic_id=#{topic_id}," +
            "teacher_id=#{teacher_id}," +
            "create_time=#{create_time}," +
            "project_note=#{project_note} " +
            "where project_id=#{project_id}")
    int updateProject(Project project);


    //删除项目
    @Delete("delete from project where project_id=#{project_id}")
    int deleteProject(Project project);
    //根据项目id查取该项目的项目信息
    @Select("select * from project where project_id=#{project_id}")
    Project getProjectById(Integer project_id);


    //根据学校编号查学校信息
    @Select("SELECT * FROM school WHERE school_id = #{school_id}")
    School getSchoolById(Integer school_id);

    //根据学院编号查询学院信息
    @Select("SELECT * FROM college WHERE college_id = #{college_id}")
    College getCollegeById(Integer college_id);

    //根据专业编号查询专业信息
    @Select("SELECT * FROM major WHERE major_id = #{major_id}")
    Major getMajorById(Integer majorId);
    //根据年级编号查询年级信息
    @Select("SELECT * FROM grade WHERE grade_id = #{grade_id}")
    Grade getGradeById(Integer gradeId);
    //根据老师id查所有项目信息
    @Select("select * from project where teacher_id = #{teacher_id}")
    List<Project> getProjectByTeacherId(Integer teacher_id);

    //根据学校、学院、专业、年级查取所有项目信息
    @Select("SELECT * FROM project WHERE school_id = #{school_id} AND college_id = #{college_id} AND major_id = #{major_id} AND grade_id = #{grade_id}")
    List<Project> getProjectsByIds(@Param("school_id") Integer school_id, @Param("college_id") Integer college_id, @Param("major_id") Integer major_id, @Param("grade_id") Integer grade_id);

    //查询正在实训的学生人数
    @Select("SELECT COUNT(DISTINCT s.student_id) AS Now_students\n" +
            "FROM project p\n" +
            "JOIN student s \n" +
            "    ON p.school_id = s.school_id\n" +
            "    AND p.college_id = s.college_id\n" +
            "    AND p.major_id = s.major_id\n" +
            "    AND p.grade_id = s.grade_id\n" +
            "WHERE CURDATE() BETWEEN p.project_start AND p.project_end;")
    Integer getNowStudents();

    @Select("SELECT * FROM project WHERE school_id = #{school_id} AND college_id = #{college_id} AND major_id = #{major_id} AND grade_id = #{grade_id}")
    List<Project> getProjectsByStudentInfo(@Param("school_id") Integer schoolId,
                                           @Param("college_id") Integer collegeId,
                                           @Param("major_id") Integer majorId,
                                           @Param("grade_id") Integer gradeId);

    //根据项目id查找项目信息
    @Select("SELECT * FROM project WHERE project_id = #{projectId}")
    Project getProjectByProjectId(Integer projectId);
}



